//=====================================================================
//
// THIS CODE AND INFORMATION IS PROVIDED TO YOU FOR YOUR REFERENTIAL
// PURPOSES ONLY, AND IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
// EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
// WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE,
// AND MAY NOT BE REDISTRIBUTED IN ANY MANNER.
//
// Copyright (C) 2003  Microsoft Corporation.  All rights reserved.
//
//=====================================================================
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

using System.Configuration;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Web.Services;

using SqlAdmin;

namespace SqlWebAdmin
{
    /// <summary>
    /// Import, build or save custom queries. Queries can also be executed on the
    /// currently selected database.
    /// </summary>
    public partial class query : System.Web.UI.Page
    {

        public query()
        {
            Page.Init += new System.EventHandler(Page_Init);
        }

        protected void Page_Load(object sender, System.EventArgs e)
        {
            ResultsPanel.Visible = false;
            ErrorLabel.Visible = false;

            List<string> staticKeywords = GetStaticKeywords();
            staticKeywords.Sort();
            List<string> tableNames = GetTableNames();
            tableNames.Sort();
            List<string> columnNames = GetColumnNames();
            columnNames.Sort();

            Session.Add("staticKeywords", staticKeywords);
            Session.Add("tableNames", tableNames);
            Session.Add("columnNames", columnNames);

            if (this.IsPostBack != true)
            {
                fillNamesList(false);
            }
            
        }

        protected void Page_Init(object sender, EventArgs e)
        {
            if (Page.User.Identity.IsAuthenticated)
            {
                Page.ViewStateUserKey = Page.Session.SessionID;
            }
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            InitializeComponent();
        }

        #region Web Form Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
        }
        #endregion

        protected void SaveButton_Click(object sender, System.EventArgs e)
        {
            // Dump out special header and the file content and end the response
            Response.Clear();
            Response.ClearHeaders();
            Response.ClearContent();

            // This header (RFC 1806) lets us set the suggested filename
            Response.AddHeader("Content-Disposition", "attachment; filename=query.sql");
            Response.Write(QueryTextbox.Text);
            Response.End();
        }

        protected void LoadButton_Click(object sender, System.EventArgs e)
        {
            // Grab file from post data
            HttpPostedFile file = FileUploadInput.PostedFile;

            int length = file.ContentLength;

            byte[] buff = new byte[length];
            file.InputStream.Read(buff, 0, length);

            // Convert from byte array to string
            StringBuilder qsb = new StringBuilder();
            for (int i = 0; i < length; i++)
                qsb.Append(Convert.ToChar(buff[i]));

            QueryTextbox.Text = qsb.ToString();
        }

        protected void ExecuteButton_Click(object sender, System.EventArgs e)
        {
            if (QueryTextbox.Text.Trim().Length == 0)
            {
                ResultsPanel.Visible = false;
                ErrorLabel.Visible = true;
                ErrorLabel.Text = "You must enter a non-empty query";
                return;
            }

            SqlServer server = SqlServer.CurrentServer;
            try
            {
                server.Connect();
            }
            catch (System.Exception ex)
            {
                Response.Redirect("Error.aspx?errorPassCode=" + 2002);
            }

            SqlDatabase database = SqlDatabase.CurrentDatabase(server);

            DataTable[] tables = null;

            try
            {
                tables = database.Query(QueryTextbox.Text);
            }
            catch (SqlException ex)
            {
                // Print error message
                ResultsPanel.Visible = false;
                ErrorLabel.Visible = true;
                ErrorLabel.Text =
                    "The following error occured while executing the query:<br>\n" +
                    String.Format("Server: Msg {0}, Level {1}, State {2}, Line {3}<br>\n", new object[] { ex.Number, ex.Class, ex.State, ex.LineNumber }) +
                    Server.HtmlEncode(ex.Message).Replace("\n", "<br>") + "<br>\n";
            }

            server.Disconnect();


            // Print output tables, if they exist
            if (tables != null)
            {
                // Add header text "Results:"
                Label label = new Label();
                label.Text = "<br><br>";
                ResultsPanel.Controls.Add(label);

                // Loop through all the tables in the DataSet
                for (int i = 0; i < tables.Length; i++)
                {
                    // Only print divider after first table
                    if (i > 0)
                    {
                        // Create new label for grid divider
                        label = new Label();
                        label.Text = "<br><br><hr><br><br>";
                        ResultsPanel.Controls.Add(label);
                    }

                    DataGrid dataGrid = new DataGrid();
                    dataGrid.HeaderStyle.CssClass = "tableHeader";
                    //dataGrid.ItemStyle.CssClass = "tableItems";
                    dataGrid.ItemStyle.BackColor = System.Drawing.Color.AliceBlue;
                    dataGrid.ItemStyle.Wrap = false;
                    dataGrid.Width = Unit.Percentage(100);
                    dataGrid.EnableViewState = false;

                    dataGrid.PreRender += new EventHandler(DataGrid_PreRender);

                    dataGrid.DataSource = tables[i];
                    dataGrid.DataBind();

                    ResultsPanel.Controls.Add(dataGrid);
                }

                ResultsPanel.Visible = true;
                ErrorLabel.Visible = false;
            }
        }

        private void DataGrid_PreRender(object sender, EventArgs e)
        {
            // Set the wrapping style of all the cells based on the checkbox, and HTML encode all the cell contents

            DataGrid d = (DataGrid)sender;

            foreach (DataGridItem item in d.Items)
            {
                foreach (TableCell t in item.Cells)
                {
                    t.Wrap = WrapCheckBox.Checked;
                    t.Text = Server.HtmlEncode(t.Text);
                }
            }
        }

        public List<string> GetTableNames()
        {
            if (chkIntellisense.Checked && chkTableNames.Checked)
            {
                #region Dynamic Database Table List
                // Dynamic Database Table List
                // Adding table names to AutoComplete list

                SqlServer server = SqlServer.CurrentServer;
                try
                {
                    server.Connect();
                }
                catch (System.Exception ex)
                {
                    Response.Redirect("Error.aspx?errorPassCode=" + 2002);
                }

                SqlDatabase database;
                SqlTableCollection tables;
                SqlObjectType objectTypeFilter;

                try
                {
                    database = SqlDatabase.CurrentDatabase(server);
                    tables = database.Tables;
                    objectTypeFilter = SqlObjectType.User;
                }
                catch
                {
                    return new List<string>(0);
                }
                List<string> tableNames = new List<string>(0);

                for (int i = 0; i < tables.Count; i++)
                {
                    SqlTable table = tables[i];
                    if ((table.TableType & objectTypeFilter) > 0)
                    {
                        tableNames.Add(table.Name);
                    }
                }

                server.Disconnect();

                return tableNames;

                #endregion
            }
            else
            {
                return new List<string>(0);
            }

        }
        public List<string> GetColumnNames()
        {

            if (chkIntellisense.Checked && chkColumnNames.Checked)
            {
                #region Dynamic Database Column List
                // Dynamic Database Table List
                // Adding table names to AutoComplete list

                SqlServer server = SqlServer.CurrentServer;
                try
                {
                    server.Connect();
                }
                catch (System.Exception ex)
                {
                    Response.Redirect("Error.aspx?errorPassCode=" + 2002);
                }

                SqlDatabase database;
                SqlTableCollection tables;
                SqlColumnCollection columns;
                SqlObjectType objectTypeFilter;

                try
                {
                    database = SqlDatabase.CurrentDatabase(server);
                    tables = database.Tables;
                    objectTypeFilter = SqlObjectType.User;
                }
                catch
                {
                    return new List<string>(0);
                }
                List<string> columnNames = new List<string>(0);

                for (int i = 0; i < tables.Count; i++)
                {
                    SqlTable table = tables[i];
                    columns = table.Columns;
                    if ((table.TableType & objectTypeFilter) > 0)
                    {
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            columnNames.Add(table.Columns[j].ColumnInformation.Name);
                        }
                    }
                }

                server.Disconnect();

                #endregion

                return columnNames;
            }
            else
            {
                return new List<string>(0);
            }
        }
        public List<string> GetStaticKeywords()
        {
            List<string> items = new List<string>(0);
            if (chkIntellisense.Checked && chkKeywords.Checked)
            {
                #region Static SQL Keywords
                // Static SQL Keywords Populated from MSDN BOL as of 07.03.2007
                // Adding keywords to the list for AutoComplete options
                items.Add("ADD");
                items.Add("EXISTS");
                items.Add("PRIMARY");
                items.Add("ALL");
                items.Add("EXIT");
                items.Add("PRINT");
                items.Add("ALTER");
                items.Add("EXTERNAL");
                items.Add("PROC");
                items.Add("AND");
                items.Add("FETCH");
                items.Add("PROCEDURE");
                items.Add("ANY");
                items.Add("FILE");
                items.Add("PUBLIC");
                items.Add("AS");
                items.Add("FILLFACTOR");
                items.Add("RAISERROR");
                items.Add("ASC");
                items.Add("FOR");
                items.Add("READ");
                items.Add("AUTHORIZATION");
                items.Add("FOREIGN");
                items.Add("READTEXT");
                items.Add("BACKUP");
                items.Add("FREETEXT");
                items.Add("RECONFIGURE");
                items.Add("BEGIN");
                items.Add("FREETEXTTABLE");
                items.Add("REFERENCES");
                items.Add("BETWEEN");
                items.Add("FROM");
                items.Add("REPLICATION");
                items.Add("BREAK");
                items.Add("FULL");
                items.Add("RESTORE");
                items.Add("BROWSE");
                items.Add("FUNCTION");
                items.Add("RESTRICT");
                items.Add("BULK");
                items.Add("GOTO");
                items.Add("RETURN");
                items.Add("BY");
                items.Add("GRANT");
                items.Add("REVERT");
                items.Add("CASCADE");
                items.Add("GROUP");
                items.Add("REVOKE");
                items.Add("CASE");
                items.Add("HAVING");
                items.Add("RIGHT");
                items.Add("CHECK");
                items.Add("HOLDLOCK");
                items.Add("ROLLBACK");
                items.Add("CHECKPOINT");
                items.Add("IDENTITY");
                items.Add("ROWCOUNT");
                items.Add("CLOSE");
                items.Add("IDENTITY_INSERT");
                items.Add("ROWGUIDCOL");
                items.Add("CLUSTERED");
                items.Add("IDENTITYCOL");
                items.Add("RULE");
                items.Add("COALESCE");
                items.Add("IF");
                items.Add("SAVE");
                items.Add("COLLATE");
                items.Add("IN");
                items.Add("SCHEMA");
                items.Add("COLUMN");
                items.Add("INDEXIN");
                items.Add("SECURITYAUDIT");
                items.Add("COMMIT");
                items.Add("INNERINDEX");
                items.Add("SELECT");
                items.Add("COMPUTE");
                items.Add("INSERTINNER");
                items.Add("SESSION_USER");
                items.Add("CONSTRAINT");
                items.Add("INTERSECTINSERT");
                items.Add("SET");
                items.Add("CONTAINS");
                items.Add("INTOINTERSECT");
                items.Add("SETUSER");
                items.Add("CONTAINSTABLE");
                items.Add("ISINTO");
                items.Add("SHUTDOWN");
                items.Add("CONTINUE");
                items.Add("JOINIS");
                items.Add("SOME");
                items.Add("CONVERT");
                items.Add("KEYJOIN");
                items.Add("STATISTICS");
                items.Add("CREATE");
                items.Add("KILLKEY");
                items.Add("SYSTEM_USER");
                items.Add("CROSS");
                items.Add("LEFTKILL");
                items.Add("TABLE");
                items.Add("CURRENT");
                items.Add("LIKELEFT");
                items.Add("TABLESAMPLE");
                items.Add("CURRENT_DATE");
                items.Add("LINENOLIKE");
                items.Add("TEXTSIZE");
                items.Add("CURRENT_TIME");
                items.Add("LOADLINENO");
                items.Add("THEN");
                items.Add("CURRENT_TIMESTAMP");
                items.Add("NATIONAL LOAD");
                items.Add("TO");
                items.Add("CURRENT_USER");
                items.Add("NOCHECKNATIONAL");
                items.Add("TOP");
                items.Add("CURSOR");
                items.Add("NONCLUSTEREDNOCHECK");
                items.Add("TRAN");
                items.Add("DATABASE");
                items.Add("NOTNONCLUSTERED");
                items.Add("TRANSACTION");
                items.Add("DBCC");
                items.Add("NULLNOT");
                items.Add("TRIGGER");
                items.Add("DEALLOCATE");
                items.Add("NULLIFNULL");
                items.Add("TRUNCATE");
                items.Add("DECLARE");
                items.Add("OFNULLIF");
                items.Add("TSEQUAL");
                items.Add("DEFAULT");
                items.Add("OFFOF");
                items.Add("UNION");
                items.Add("DELETE");
                items.Add("OFFSETSOFF");
                items.Add("UNIQUE");
                items.Add("DENY");
                items.Add("ONOFFSETS");
                items.Add("UNPIVOT");
                items.Add("DESC");
                items.Add("OPENON");
                items.Add("UPDATE");
                items.Add("DISK");
                items.Add("OPENDATASOURCEOPEN");
                items.Add("UPDATETEXT");
                items.Add("DISTINCT");
                items.Add("OPENQUERYOPENDATASOURCE");
                items.Add("USE");
                items.Add("DISTRIBUTED");
                items.Add("OPENROWSETOPENQUERY");
                items.Add("USER");
                items.Add("DOUBLE");
                items.Add("OPENXMLOPENROWSET");
                items.Add("VALUES");
                items.Add("DROP");
                items.Add("OPTIONOPENXML");
                items.Add("VARYING");
                items.Add("DUMP");
                items.Add("OROPTION");
                items.Add("VIEW");
                items.Add("ELSE");
                items.Add("ORDEROR");
                items.Add("WAITFOR");
                items.Add("END");
                items.Add("OUTERORDER");
                items.Add("WHEN");
                items.Add("ERRLVL");
                items.Add("OVEROUTER");
                items.Add("WHERE");
                items.Add("ESCAPE");
                items.Add("PERCENTOVER");
                items.Add("WHILE");
                items.Add("EXCEPT");
                items.Add("PIVOTPERCENT");
                items.Add("WITH");
                items.Add("EXEC");
                items.Add("PLANPIVOT");
                items.Add("WRITETEXT");
                items.Add("EXECUTE");
                items.Add("PLAN PRECISION");
                #endregion

                #region Static ODBC Keywords
                // Static ODBC Keywords populated from MSDN BOL as of 07.03.07
                // Adding keywords to the list of AutoComplete options
                items.Add("ABSOLUTE");
                items.Add("ACTION");
                items.Add("ADA");
                items.Add("ALLOCATE");
                items.Add("ARE");
                items.Add("ASSERTION");
                items.Add("AT");
                items.Add("AVG");
                items.Add("BIT");
                items.Add("BIT_LENGTH");
                items.Add("BOTH");
                items.Add("CASCADED");
                items.Add("CAST");
                items.Add("CATALOG");
                items.Add("CHAR");
                items.Add("CHAR_LENGTH");
                items.Add("CHARACTER");
                items.Add("CHARACTER_LENGTH");
                items.Add("COLLATION");
                items.Add("CONNECT");
                items.Add("CONNECTION");
                items.Add("CONSTRAINTS");
                items.Add("CORRESPONDING");
                items.Add("COUNT");
                items.Add("DATE");
                items.Add("DAY");
                items.Add("DEC");
                items.Add("DECIMAL");
                items.Add("DEFERRABLE");
                items.Add("DEFERRED");
                items.Add("DESCRIBE");
                items.Add("DESCRIPTOR");
                items.Add("DIAGNOSTICS");
                items.Add("DISCONNECT");
                items.Add("DOMAIN");
                items.Add("END-EXEC");
                items.Add("EXCEPTION");
                items.Add("EXTRACT");
                items.Add("FALSE");
                items.Add("FIRST");
                items.Add("FLOAT");
                items.Add("FORTRAN");
                items.Add("FOUND");
                items.Add("GET");
                items.Add("GLOBAL");
                items.Add("GO");
                items.Add("HOUR");
                items.Add("IMMEDIATE");
                items.Add("INCLUDE");
                items.Add("INDEX");
                items.Add("INDICATOR");
                items.Add("INITIALLY");
                items.Add("INNER");
                items.Add("INPUT");
                items.Add("INSENSITIVE");
                items.Add("INSERT");
                items.Add("INT");
                items.Add("INTEGER");
                items.Add("INTERSECT");
                items.Add("INTERVAL");
                items.Add("INTO");
                items.Add("IS");
                items.Add("ISOLATION");
                items.Add("JOIN");
                items.Add("KEY");
                items.Add("LANGUAGE");
                items.Add("LAST");
                items.Add("LEADING");
                items.Add("LEFT");
                items.Add("LEVEL");
                items.Add("LIKE");
                items.Add("LOCAL");
                items.Add("LOWER");
                items.Add("MATCH");
                items.Add("MAX");
                items.Add("MIN");
                items.Add("MINUTE");
                items.Add("MODULE");
                items.Add("MONTH");
                items.Add("NAMES");
                items.Add("NATIONAL");
                items.Add("NATURAL");
                items.Add("NCHAR");
                items.Add("NEXT");
                items.Add("NO");
                items.Add("NONE");
                items.Add("NOT");
                items.Add("NULL");
                items.Add("NULLIF");
                items.Add("NUMERIC");
                items.Add("OCTET_LENGTH");
                items.Add("OF");
                items.Add("ON");
                items.Add("ONLY");
                items.Add("OPEN");
                items.Add("OPTION");
                items.Add("OR");
                items.Add("ORDER");
                items.Add("OUTER");
                items.Add("OUTPUT");
                items.Add("OVERLAPS");
                items.Add("PAD");
                items.Add("PARTIAL");
                items.Add("PASCAL");
                items.Add("POSITION");
                items.Add("PRECISION");
                items.Add("PREPARE");
                items.Add("PRESERVE");
                items.Add("PRIOR");
                items.Add("PRIVILEGES");
                items.Add("REAL");
                items.Add("RELATIVE");
                items.Add("ROWS");
                items.Add("SCROLL");
                items.Add("SECOND");
                items.Add("SECTION");
                items.Add("SESSION");
                items.Add("SIZE");
                items.Add("SMALLINT");
                items.Add("SPACE");
                items.Add("SQL");
                items.Add("SQLCA");
                items.Add("SQLCODE");
                items.Add("SQLERROR");
                items.Add("SQLSTATE");
                items.Add("SQLWARNING");
                items.Add("SUBSTRING");
                items.Add("SUM");
                items.Add("TEMPORARY");
                items.Add("TIME");
                items.Add("TIMESTAMP");
                items.Add("TIMEZONE_HOUR");
                items.Add("TIMEZONE_MINUTE");
                items.Add("TRAILING");
                items.Add("TRANSLATE");
                items.Add("TRANSLATION");
                items.Add("TRIM");
                items.Add("TRUE");
                items.Add("UNKNOWN");
                items.Add("UPPER");
                items.Add("USAGE");
                items.Add("USING");
                items.Add("VALUE");
                items.Add("VARCHAR");
                items.Add("WHENEVER");
                items.Add("WORK");
                items.Add("WRITE");
                items.Add("YEAR");
                items.Add("ZONE");


                #endregion
            }
            return items;
        }
        protected void fillNamesList(bool isColumns)
        {
            string selectedTable;
            if (NamesCheckBox1.Checked)
            {
                NamesListBox.Visible = true;
                if (isColumns)
                {
                    selectedTable = (string)NamesListBox.Items[NamesListBox.SelectedIndex].Value;
                    #region Load Database Column List
                    // Dynamic Database Table List
                    // Adding table names to Names list

                    SqlServer server = SqlServer.CurrentServer;
                    try
                    {
                        server.Connect();
                    }
                    catch (System.Exception ex)
                    {
                        Response.Redirect("Error.aspx?errorPassCode=" + 2002);
                    }

                    SqlDatabase database;
                    SqlTableCollection tables;
                    SqlColumnCollection columns;
                    SqlObjectType objectTypeFilter;

                    List<string> columnNames = new List<string>(0);

                    try
                    {
                        database = SqlDatabase.CurrentDatabase(server);
                        tables = database.Tables;
                        objectTypeFilter = SqlObjectType.User;

                        SqlTable table = tables[selectedTable];
                        columns = table.Columns;
                        if ((table.TableType & objectTypeFilter) > 0)
                        {
                            for (int j = 0; j < table.Columns.Count; j++)
                            {
                                columnNames.Add(table.Columns[j].ColumnInformation.Name);
                            }
                        }
                    }
                    catch
                    {
                        server.Disconnect();
                        Response.Redirect("Error.aspx?errorPassCode=" + 2002);
                    }

                    server.Disconnect();

                    #endregion
                    ColumnsListBox.DataSource = columnNames;
                    ColumnsListBox.DataBind();
                    ColumnsListBox.Visible = true;
                }
                else
                {
                    #region Load Database Table List
                    // Dynamic Database Table List
                    // Adding table names to Names list

                    SqlServer server = SqlServer.CurrentServer;
                    try
                    {
                        server.Connect();
                    }
                    catch (System.Exception ex)
                    {
                        Response.Redirect("Error.aspx?errorPassCode=" + 2002);
                    }

                    SqlDatabase database;
                    SqlTableCollection tables;
                    SqlObjectType objectTypeFilter;

                    List<string> tableNames = new List<string>(0);

                    try
                    {
                        database = SqlDatabase.CurrentDatabase(server);
                        tables = database.Tables;
                        objectTypeFilter = SqlObjectType.User;

                        for (int i = 0; i < tables.Count; i++)
                        {
                            SqlTable table = tables[i];
                            if ((table.TableType & objectTypeFilter) > 0)
                            {
                                tableNames.Add(table.Name);
                            }
                        }
                    }
                    catch
                    {
                        server.Disconnect();
                        Response.Redirect("Error.aspx?errorPassCode=" + 2002);
                    }



                    server.Disconnect();

                    #endregion
                    NamesListBox.DataSource = tableNames;
                    NamesListBox.DataBind();
                    ColumnsListBox.Visible = false;
                }

            }
            else
            {
                NamesListBox.Visible = false;
                ColumnsListBox.Visible = false;
            }
        }
        protected void chkIntellisense_CheckedChanged(object sender, EventArgs e)
        {
            if (chkIntellisense.Checked)
            {
                chkKeywords.Enabled = true;
                chkTableNames.Enabled = true;
                chkColumnNames.Enabled = true;
            }
            else
            {
                chkKeywords.Enabled = false;
                chkTableNames.Enabled = false;
                chkColumnNames.Enabled = false;
            }

        }
        
        protected void chkKeywords_CheckedChanged(object sender, EventArgs e)
        {

        }
        protected void chkTableNames_CheckedChanged(object sender, EventArgs e)
        {

        }
        protected void chkColumnNames_CheckedChanged(object sender, EventArgs e)
        {

        }

        protected void NamesCheckBox1_CheckedChanged(object sender, EventArgs e)
        {
            fillNamesList(false);
        }
        protected void NamesListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Show and fill column names list with colums of selected table
            fillNamesList(true);
        }
        protected void ColumnsListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Insert selected index into query string
            QueryTextbox.Text += ColumnsListBox.SelectedValue;
        }
}
}
